package com.kreao.korean.infrastructure.service;

import com.kreao.korean.entity.DatabaseMigration;
import com.kreao.korean.repository.DatabaseMigrationRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 数据库迁移服务
 * 负责扫描、执行和管理数据库迁移脚本
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DatabaseMigrationService {

    private final DatabaseMigrationRepository migrationRepository;
    private final JdbcTemplate jdbcTemplate;
    
    // 迁移脚本文件名格式：V{version}__{description}.sql
    private static final Pattern MIGRATION_PATTERN = Pattern.compile("^V(\\d+)__(.+)\\.sql$");
    private static final String MIGRATION_PATH = "classpath:db/migration/*.sql";

    /**
     * 执行所有待处理的迁移脚本
     */
    @Transactional
    public void migrate() {
        log.info("开始执行数据库迁移...");
        
        try {
            // 确保迁移表存在
            ensureMigrationTableExists();
            
            // 扫描迁移脚本
            List<MigrationScript> scripts = scanMigrationScripts();
            
            // 获取已执行的迁移记录
            Set<String> executedVersions = getExecutedVersions();
            
            // 过滤出需要执行的脚本
            List<MigrationScript> pendingScripts = scripts.stream()
                .filter(script -> !executedVersions.contains(script.getVersion()))
                .sorted(Comparator.comparing(MigrationScript::getVersion))
                .collect(Collectors.toList());
            
            if (pendingScripts.isEmpty()) {
                log.info("没有需要执行的迁移脚本");
                return;
            }
            
            log.info("发现 {} 个待执行的迁移脚本", pendingScripts.size());
            
            // 执行迁移脚本
            for (MigrationScript script : pendingScripts) {
                executeMigration(script);
            }
            
            log.info("数据库迁移完成！");
            
        } catch (Exception e) {
            log.error("数据库迁移失败", e);
            throw new RuntimeException("数据库迁移失败: " + e.getMessage(), e);
        }
    }

    /**
     * 确保迁移表存在
     */
    private void ensureMigrationTableExists() {
        try {
            String checkTableSql = """
                SELECT COUNT(*) FROM information_schema.tables 
                WHERE table_schema = DATABASE() AND table_name = 'database_migrations'
                """;
            
            Integer count = jdbcTemplate.queryForObject(checkTableSql, Integer.class);
            
            if (count == null || count == 0) {
                log.info("迁移表不存在，正在创建...");
                String createTableSql = """
                    CREATE TABLE database_migrations (
                        id BIGINT AUTO_INCREMENT PRIMARY KEY,
                        version VARCHAR(100) NOT NULL UNIQUE COMMENT '迁移脚本版本号',
                        description VARCHAR(200) NOT NULL COMMENT '迁移脚本描述',
                        filename VARCHAR(100) NOT NULL COMMENT '迁移脚本文件名',
                        checksum VARCHAR(64) NOT NULL COMMENT '脚本内容校验和',
                        executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '执行时间',
                        execution_time BIGINT NOT NULL COMMENT '执行耗时（毫秒）',
                        status ENUM('SUCCESS', 'FAILED', 'PENDING') NOT NULL DEFAULT 'PENDING' COMMENT '执行状态',
                        error_message TEXT COMMENT '错误信息',
                        INDEX idx_version (version),
                        INDEX idx_status (status),
                        INDEX idx_executed_at (executed_at)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='数据库迁移记录表'
                    """;
                jdbcTemplate.execute(createTableSql);
                log.info("迁移表创建成功");
            }
        } catch (Exception e) {
            log.error("检查或创建迁移表失败", e);
            throw new RuntimeException("迁移表初始化失败", e);
        }
    }

    /**
     * 扫描迁移脚本文件
     */
    private List<MigrationScript> scanMigrationScripts() throws Exception {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(MIGRATION_PATH);
        
        List<MigrationScript> scripts = new ArrayList<>();
        
        for (Resource resource : resources) {
            String filename = resource.getFilename();
            if (filename == null) continue;
            
            Matcher matcher = MIGRATION_PATTERN.matcher(filename);
            if (!matcher.matches()) {
                log.warn("忽略格式不正确的迁移文件: {}", filename);
                continue;
            }
            
            String version = "V" + matcher.group(1);
            String description = matcher.group(2).replace("_", " ");
            
            // 读取文件内容
            String content = readResourceContent(resource);
            String checksum = calculateChecksum(content);
            
            scripts.add(MigrationScript.builder()
                .version(version)
                .description(description)
                .filename(filename)
                .content(content)
                .checksum(checksum)
                .build());
        }
        
        return scripts;
    }

    /**
     * 读取资源文件内容
     */
    private String readResourceContent(Resource resource) throws Exception {
        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(resource.getInputStream(), StandardCharsets.UTF_8))) {
            return reader.lines().collect(Collectors.joining("\n"));
        }
    }

    /**
     * 计算文件内容的MD5校验和
     */
    private String calculateChecksum(String content) {
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] hash = md.digest(content.getBytes(StandardCharsets.UTF_8));
            StringBuilder sb = new StringBuilder();
            for (byte b : hash) {
                sb.append(String.format("%02x", b));
            }
            return sb.toString();
        } catch (Exception e) {
            throw new RuntimeException("计算校验和失败", e);
        }
    }

    /**
     * 获取已执行的迁移版本
     */
    private Set<String> getExecutedVersions() {
        try {
            return migrationRepository.findAllSuccessfulMigrations()
                .stream()
                .map(DatabaseMigration::getVersion)
                .collect(Collectors.toSet());
        } catch (Exception e) {
            // 如果表不存在，返回空集合
            return new HashSet<>();
        }
    }

    /**
     * 执行单个迁移脚本
     */
    @Transactional
    public void executeMigration(MigrationScript script) {
        log.info("执行迁移脚本: {} - {}", script.getVersion(), script.getDescription());
        
        long startTime = System.currentTimeMillis();
        DatabaseMigration migration = null;
        
        try {
            // 执行SQL脚本
            log.info("开始执行SQL脚本内容...");
            executeSqlStatements(script.getContent());
            log.info("SQL脚本执行完成");
            
            // 创建迁移记录
            migration = DatabaseMigration.builder()
                .version(script.getVersion())
                .description(script.getDescription())
                .filename(script.getFilename())
                .checksum(script.getChecksum())
                .executedAt(LocalDateTime.now())
                .status(DatabaseMigration.MigrationStatus.SUCCESS)
                .build();
            
            // 更新执行成功状态
            long executionTime = System.currentTimeMillis() - startTime;
            migration.setExecutionTime(executionTime);
            migration.setStatus(DatabaseMigration.MigrationStatus.SUCCESS);
            migrationRepository.save(migration);
            
            log.info("迁移脚本 {} 执行成功，耗时: {}ms", script.getVersion(), executionTime);
            
        } catch (Exception e) {
            long executionTime = System.currentTimeMillis() - startTime;
            log.error("迁移脚本 {} 执行失败", script.getVersion(), e);
            
            // 更新失败状态
            if (migration != null) {
                migration.setExecutionTime(executionTime);
                migration.setStatus(DatabaseMigration.MigrationStatus.FAILED);
                migration.setErrorMessage(e.getMessage());
                migrationRepository.save(migration);
            }
            
            throw new RuntimeException("迁移脚本执行失败: " + script.getVersion(), e);
        }
    }

    /**
     * 执行SQL语句
     */
    private void executeSqlStatements(String content) {
        // 移除注释并按语句分割
        String[] lines = content.split("\n");
        StringBuilder currentStatement = new StringBuilder();
        
        for (String line : lines) {
            String trimmedLine = line.trim();
            
            // 跳过空行和注释行
            if (trimmedLine.isEmpty() || trimmedLine.startsWith("--")) {
                continue;
            }
            
            currentStatement.append(line).append("\n");
            
            // 如果行以分号结尾，执行当前语句
            if (trimmedLine.endsWith(";")) {
                String statement = currentStatement.toString().trim();
                if (!statement.isEmpty()) {
                    // 移除末尾的分号
                    statement = statement.substring(0, statement.length() - 1);
                    try {
                        log.debug("执行SQL: {}", statement);
                        jdbcTemplate.execute(statement);
                    } catch (Exception e) {
                        log.error("SQL执行失败: {}", statement, e);
                        throw e;
                    }
                }
                currentStatement = new StringBuilder();
            }
        }
        
        // 处理最后一个不以分号结尾的语句
        String finalStatement = currentStatement.toString().trim();
        if (!finalStatement.isEmpty()) {
            try {
                log.debug("执行最后的SQL: {}", finalStatement);
                jdbcTemplate.execute(finalStatement);
            } catch (Exception e) {
                log.error("最后SQL执行失败: {}", finalStatement, e);
                throw e;
            }
        }
    }

    /**
     * 获取迁移历史
     */
    public List<DatabaseMigration> getMigrationHistory() {
        return migrationRepository.findAll();
    }

    /**
     * 迁移脚本数据结构
     */
    @lombok.Data
    @lombok.Builder
    public static class MigrationScript {
        private String version;
        private String description;
        private String filename;
        private String content;
        private String checksum;
    }
} 